package common;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.io.Reader;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.log4j.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

public class DbUtil {
	private static final Logger LOG = Logger.getLogger(DbUtil.class);
	public static JdbcTemplate jdbcTemplate=null;
	
	public static JdbcTemplate getJdbcTemplate(){
		if(jdbcTemplate==null){
			ApplicationContext ctx = new ClassPathXmlApplicationContext("jdbc-context.xml");
			jdbcTemplate= (JdbcTemplate)ctx.getBean("jdbcTemplate");
		}		
		return jdbcTemplate;
	}
	
	public  static Connection  getConnection(){
		Connection conn=null;
		try {
			 conn=getJdbcTemplate().getDataSource().getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * 标准查询，无参数
	 * 
	 * @param <T>
	 * @param sql
	 * @param rsh
	 * @return
	 * @throws Exception
	 */
	public static <T> T executeQuery(String sql, ResultSetHandler<T> rsh) {
		return executeQuery(sql, null, rsh);
	}

	/**
	 * 标准查询，无参数
	 * 
	 * @param <T>
	 * @param conn
	 * @param sql
	 * @param rsh
	 * @return
	 * @throws Exception
	 */
	public static <T> T executeQuery(Connection conn, String sql, ResultSetHandler<T> rsh) throws Exception {
		return executeQuery(conn, sql, null, rsh);
	}

	/**
	 * 标准查询，有参数
	 * 
	 * @param <T>
	 * @param sql
	 * @param rsh
	 * @return
	 * @throws Exception
	 */
	public static <T> T executeQuery(String sql, ArrayList<Object> params, ResultSetHandler<T> rsh)   {
		ResultSet rs = null;
		T result = null;
		try {
			rs=queryResultSet(sql, params, false);
			result = rsh.handle(rs);
		} catch (Exception e) {
			LOG.info("出错语句:" + sql, e);
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					LOG.info("出错语句:" + sql, e);
					e.printStackTrace();
				}
			}
		}
		return result;
	}
	
	/**
	 * 标准查询，有参数
	 * 
	 * @param <T>
	 * @param sql
	 * @param rsh
	 * @return
	 * @throws Exception
	 */
	public static <T> T executeQuery(Connection conn,String sql, ArrayList<Object> params, ResultSetHandler<T> rsh) {
		ResultSet rs = null;
		T result = null;
		try {
			rs=queryResultSet(sql, params, false);
			// ResultSetMetaData rsmd = rs.getMetaData();
			result = rsh.handle(rs);
		} catch (Exception e) {
			LOG.info("出错语句:" + sql, e);
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					LOG.info("出错语句:" + sql, e);
					e.printStackTrace();
				}
			}
		}
		return result;
	}

/**
 * 获取结果集
 * @param sql
 * @param params
 * @return
 * @throws Exception
 */	
	protected static ResultSet queryResultSet(String sql, List params) throws Exception {
		return queryResultSet(sql, params, true);
	}
/**
 * 获取结果集
 * @param sql
 * @param params
 * @param scrollable
 * @return
 * @throws Exception 
 */
	protected static ResultSet queryResultSet(String sql, List params, boolean scrollable) throws Exception {
		Connection conn = null;
		PreparedStatement queryStmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			if (scrollable)
				queryStmt = conn.prepareStatement(sql, 1004, 1007);
			else {
				queryStmt = conn.prepareStatement(sql);
			}
			setParamValue(queryStmt, params);
			rs = queryStmt.executeQuery();
		} catch (Exception e) {
			LOG.info("出错语句:" + sql, e);
			e.printStackTrace();			
		}finally{
			try{
			if(conn!=null)
				conn.close();
			}catch(Exception e){
				LOG.info("出错语句:" + sql, e);
				e.printStackTrace();
			}
			}
		return rs;
	}
/**
 * 获取结果集，有连接
 * @param conn
 * @param sql
 * @param params
 * @param scrollable
 * @return
 */	
	protected static ResultSet queryResultSet(Connection conn,String sql, List params, boolean scrollable) {
		PreparedStatement queryStmt = null;
		ResultSet rs = null;
		try {
			if (scrollable)
				queryStmt = conn.prepareStatement(sql, 1004, 1007);
			else {
				queryStmt = conn.prepareStatement(sql);
			}
			setParamValue(queryStmt, params);
			rs = queryStmt.executeQuery();
			return rs;
		} catch (Exception e) {
			LOG.info("出错语句:" + sql, e);
			e.printStackTrace();
		}finally{
			try{
			if(conn!=null)
				conn.close();
			}catch (Exception e) {
				LOG.info("出错语句:" + sql, e);
				e.printStackTrace();
			}
		}
		return rs;
	}
/**
 * 置参数
 * @param theStmt
 * @param params
 * @throws Exception
 */
	private static void setParamValue(PreparedStatement theStmt, List params) throws Exception {
		setParamValue(theStmt, params, null);
	}
/**
 * 置参数
 * @param theStmt
 * @param params
 * @param type
 * @throws Exception
 */
	private static void setParamValue(PreparedStatement theStmt, List params, int[] type) throws Exception {
		if ((params == null) || (params.size() == 0)) {
			return;
		}

		if ((type != null) && (type.length == params.size())) {
			for (int i = 0; i < params.size(); i++) {
				Object value = params.get(i);
				switch (type[i]) {
				case -4:
				case -3:
				case -2:
					if (!(value instanceof byte[]))
						continue;
					InputStream is = new ByteArrayInputStream((byte[]) value);
					theStmt.setBinaryStream(i + 1, is, is.available());

					break;
				case -1:
					if (!(value instanceof String))
						continue;
					String content = value.toString();
					Reader reader = new StringReader(content);
					theStmt.setCharacterStream(i + 1, reader, content.length());

					break;
				default:
					theStmt.setObject(i + 1, value);
				}
			}
		} else
			for (int i = 0; i < params.size(); i++)
				theStmt.setObject(i + 1, params.get(i));
	}
	
	
	

	public static List query(String sql, Object[] params) {
		QueryRunner qRunner = new QueryRunner();
		List lMap=null;
		try {
			lMap = (List) qRunner.query(getConnection(), sql, new MapListHandler(), params);
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			LOG.info("出错语句:"+sql,e);e.printStackTrace();
		}
		return lMap;
	}
	
	public static int update(String sql, Object[] params) {
		QueryRunner qRunner = new QueryRunner();
		int i=0;
		try {
			i=qRunner.update(getConnection(), sql, params);
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			LOG.info("出错语句:"+sql,e);e.printStackTrace();
		}
		return i;
	}

}
